package test01;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mysql.cj.protocol.Resultset;
import org.junit.Test;
import pojo.Brand;

import javax.sql.DataSource;
import java.io.FileInputStream;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * @author Davey chan
 */
public class Brandtest {
    //    查询操作
    @Test
    public void testSelectAll() throws Exception {
//创建数据库连接

        Class.forName("com.mysql.jdbc.Driver");
//        获取连接
        String url = "jdbc:mysql:///data02?serverTimezone=GMT%2B8";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
//        定义SQL语句
        String sql = "select * from brand;";
//        执行pstmt语句
        PreparedStatement pstmt = conn.prepareStatement(sql);
//        设置参数

//        执行SQL语句
        ResultSet rs = pstmt.executeQuery();
//        处理结果
        Brand brand = null;
        List<Brand> Brands = new ArrayList<>();
        while (rs.next()) {
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int status = rs.getInt("status");
//            封装brand对象
            brand = new Brand();
            brand.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);
//           装载集合
            Brands.add(brand);
        }
        System.out.println(Brands);
//        释放资源
        rs.close();
        pstmt.close();
        conn.close();

    }


    //    添加操作
    @Test
    public void testAdd() throws Exception {
//        模拟接受参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

//创建数据库连接

        Class.forName("com.mysql.jdbc.Driver");
//        获取连接
        String url = "jdbc:mysql:///data02?serverTimezone=GMT%2B8";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
//        定义SQL语句
        String sql = "insert into brand (Brand_name,company_name,ordered,description,status)" +
                "values(?,?,?,?,?)";
//        3执行pstmt语句
        PreparedStatement pstmt = conn.prepareStatement(sql);
//        4设置参数
        pstmt.setString(1, brandName);
        pstmt.setString(2, companyName);
        pstmt.setInt(3, ordered);
        pstmt.setString(4, description);
        pstmt.setInt(5, status);


//        5执行SQL语句
        int count = pstmt.executeUpdate();
//       6 处理结果
        System.out.println(count>0);


//        释放资源

    pstmt.close();
    conn.close();

    }
//    修改操作
@Test
public void testUpdata() throws Exception {
//        模拟接受参数
    String brandName = "香飘飘";
    String companyName = "香飘飘";
    int ordered = 100;
    String description = "绕地球十圈";
    int status = 1;
    int id=4;

//创建数据库连接

    Class.forName("com.mysql.jdbc.Driver");
//        获取连接
    String url = "jdbc:mysql:///data02?serverTimezone=GMT%2B8";
    String username = "root";
    String password = "123456";
    Connection conn = DriverManager.getConnection(url, username, password);
//        定义SQL语句
    String sql = "update brand\n" +
            " set brand_name=?,\n"+
            "company_name=?,   \n "+
            "ordered=?,         \n"+
            "description=?,    \n" +
            "status=?            \n"+
            "where id=?";
//        3执行pstmt语句
    PreparedStatement pstmt = conn.prepareStatement(sql);
//        4设置参数
    pstmt.setString(1, brandName);
    pstmt.setString(2, companyName);
    pstmt.setInt(3, ordered);
    pstmt.setString(4, description);
    pstmt.setInt(5, status);
    pstmt.setInt(6,id);


//        5执行SQL语句
    int count = pstmt.executeUpdate();
//       6 处理结果
    System.out.println(count>0);
//        释放资源

    pstmt.close();
    conn.close();

}
@Test
    public void testDel() throws Exception {
//        模拟接受参数
        int id=4;
//创建数据库连接
        Class.forName("com.mysql.jdbc.Driver");
//        获取连接
        String url = "jdbc:mysql:///data02?serverTimezone=GMT%2B8";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
//        定义SQL语句
        String sql = "delete from brand where id=?";
//        3执行pstmt语句
        PreparedStatement pstmt = conn.prepareStatement(sql);
//        4设置参数
        pstmt.setInt(1,id);
//        5执行SQL语句
        int count = pstmt.executeUpdate();
//       6 处理结果
        System.out.println(count>0);
//        释放资源

        pstmt.close();
        conn.close();

    }
}
